<?php

namespace XWAM\Addon\PXCallSystem\Controller;

/**
 * 队列控制器
 *
 * @author Alen
 * @since 1.0 2014-11-21 by sutroon
 */
class SysRepairController extends AppbaseController {
    
    public function test_loadinfile(){
        $link = mysql_connect(C('DB_HOST'), C('DB_USER'), C('DB_PWD')) or die(mysql_error());
        mysql_select_db('db_xcall', $link) or die(mysql_error());
        mysql_query('set names utf8', $link) or die(mysql_error());
        mysql_query('SET UNIQUE_CHECKS=0', $link);
        $sql="load data infile '/var/www/html/xcall/Application/Runtime/Sqls/ttf_callnumber_666.sql' into table tcall_number_temp_8" . ' character set utf8 FIELDS TERMINATED BY "," ENCLOSED BY \'"\' ESCAPED BY "\\\" LINES TERMINATED BY "\n" STARTING BY "" (trunkName, taskStrategyID, taskID, phoneNumber, source, siteID)';
        mysql_query($sql);
        mysql_query('SET UNIQUE_CHECKS=1', $link);
        mysql_close($link);
        echo 'ok';
    }

    public function export_data() {
        $config = array('max_count' => 0);
        $table = 'tcti_number_start';
        $fields = M($table)->getDbFields();
        $link = mysql_connect(C('DB_HOST'), C('DB_USER'), C('DB_PWD')) or die(mysql_error());
        mysql_select_db('db_xcall_service', $link) or die(mysql_error());
        mysql_query('set names utf8', $link) or die(mysql_error());
        $res = mysql_query('select ' . implode(',', $fields) . ' from ' . $table, $link) or die(mysql_error());
        $i = 0;
        $file_normal = fopen('F:\\' . $table . '_normal.sql', 'w');
        $file_hebin = fopen('F:\\' . $table . '_hebin.sql', 'w');
        $file_infile = fopen('F:\\' . $table . '_infile.sql', 'w');
        $sqlfield = "insert into $table (id, areaName, numberPart, phoneLength, ordinal, state) values ";
        fwrite($file_hebin, "insert into tcti_number_start_hebin (id, areaName, numberPart, phoneLength, ordinal, state) values ");
        while ($row = mysql_fetch_assoc($res)) {
            if ($config['max_count'] && $i > $config['max_count']) {
                break;
            }
            $sqlvalue = $row['id'] . ',\'' . $row['areaName'] . '\',' . $row['numberPart'] . ',' . $row['phoneLength'] . ',' . $row['ordinal'] . ',' . $row['state'];
            fwrite($file_normal, 'insert into tcti_number_start_normal (id, areaName, numberPart, phoneLength, ordinal, state) values (' . $sqlvalue . ');' . PHP_EOL);
            if ($i < 5000) {
                if ($i > 0) {
                    fwrite($file_hebin, ',');
                }
                fwrite($file_hebin, '(' . $sqlvalue . ')');
            }
            fwrite($file_infile, $sqlvalue . PHP_EOL);
            $i++;
        }
        echo $i;
        fclose($file_normal);
        fclose($file_hebin);
        fclose($file_infile);
        mysql_close($link);
    }

    public function load_data($opt = '', $yh = 0) {
        ignore_user_abort();
        if (!$opt) {
            echo 'ready...';
            return;
        }
        $begin = microtime(TRUE);
        $link = mysql_connect(C('DB_HOST'), C('DB_USER'), C('DB_PWD')) or die(mysql_error());
        mysql_select_db('db_xcall', $link) or die(mysql_error());
        mysql_query('set names utf8', $link) or die(mysql_error());
        if ($yh) {
            mysql_query('SET UNIQUE_CHECKS=0', $link);
        }
        switch ($opt) {
            case 'infile':
                $table = 'tcti_number_start_infile';
                if ($yh) {
                    mysql_query("ALTER  TABLE  $table  DISABLE  KEYS", $link);
                }
                $filename = 'F:\\\\' . $table . '.sql';
                $sql = "load data infile '$filename' into table $table" . ' character set utf8 FIELDS TERMINATED BY "," ENCLOSED BY \'"\' ESCAPED BY "\\\" LINES TERMINATED BY "\n" STARTING BY "" (id,areaName,numberPart,phoneLength,ordinal,state)';
                mysql_query($sql, $link);
                if ($yh) {
                    mysql_query("ALTER  TABLE  $table  ENABLE  KEYS", $link);
                }
                break;
            case 'hebin':
                $table = 'tcti_number_start_hebin';
                $filename = 'F:\\' . $table . '.sql';
                $sql = file_get_contents($filename);
                mysql_query($sql, $link);
                break;
            default:
                $table = 'tcti_number_start_normal';
                $filename = 'F:\\' . $table . '.sql';
                $i = 0;
                if ($yh) {
                    mysql_query("ALTER  TABLE  $table  DISABLE  KEYS", $link);
                }
                $file = fopen($filename, "r");
                while (!feof($file)) {
                    $sql = fgets($file);
                    mysql_query($sql, $link);
                    $i++;
                }
                if ($yh) {
                    mysql_query("ALTER  TABLE  $table  ENABLE  KEYS", $link);
                }
                echo $i;
                break;
        }
        mysql_close($link);
        $end = microtime(TRUE);
        $time = $end - $begin;
        echo "执行了" . $time . "s";
    }

    public function update3() {
        $siteIDs = M('tuser_member')->distinct(true)->where("userType='ADMIN'")->getField('siteID', true);
        foreach ($siteIDs as $siteID) {
            AddonD('DbTable')->create_table('CALL_NUMBER', 'db_xcall_service.tcustomer_' . $siteID);
        }
        echo 'ok';
    }

    public function create_table($type = '') {
        if (!$type) {
            die('参数无效,支持cus|num');
        }
        $siteIDs = M('tuser_member')->distinct(true)->where("userType='ADMIN'")->getField('siteID', true);
        $num_db = sofn_get_sharding_field('DB_IMPORT_TEMP');
        foreach ($siteIDs as $siteID) {
            switch ($type) {
                case 'cus':
                    AddonD('DbTable')->create_table('CALL_NUMBER', 'db_xcall_service.tcustomer_' . $siteID);
                    break;
                case 'num':
                    AddonD('DbTable')->create_table('CALL_NUMBER', 'tcall_number_temp_' . $siteID,array('ENGINEE' => 'Memory'));
                    break;
            }
        }
        echo 'ok';
    }

    public function repair_cdr_amount() {
        $M = M('cdr');
        $list = $M->where("amount=0 and direction in (2,5)")->getField('accountcode', true);
        foreach ($list as $accountcode) {
            $M->execute("call proc_cdr_after_insert('$accountcode','')");
        }
        echo '统计结束';
    }

    public function batch_task_strategy_tables() {
        $id = I('id');
        if ($id) {
            AddonD('DbTable')->create_table('CALL_NUMBER', sofn_get_sharding_field('DB_CALLNUMBER') . 'tcti_call_number' . $id);
        } else {
            $list = M('tcti_task_strategy')->getField('id', true);
            foreach ($list as $id) {
                AddonD('DbTable')->create_table('CALL_NUMBER', sofn_get_sharding_field('DB_CALLNUMBER') . 'tcti_call_number' . $id);
            }
        }
        echo 'ok';
    }

    /**
     * 重新统计日结账单余额
     * @since 1.0 2014-11-21 by sutroon
     * @example /SysRepair/repair_daybill?run=1
     */
    public function repair_daybill() {
        if (!I('run')) {
            die('参数有误[run]!');
        }
        $M = M('tcti_daybill');
        $siteIDs = $M->distinct(true)->getField('siteID', true);
        foreach ($siteIDs as $siteID) {
            $list = $M->order('billdate')->where('siteID=' . $siteID)->field('id, blance, rechargeAmount, subtotal')->select();
            $blance = $list[0]['blance'];
            unset($list[0]);
            $sql = '';
            foreach ($list as $row) {
                $blance = $blance + $row['rechargeAmount'] - $row['subtotal'];
                $sql .= ',(' . $row['id'] . ',' . $blance . ')';
            }
            if ($sql) {
                $sql = substr($sql, 1);
                $sql = "insert into tcti_daybill (id, blance) values $sql on duplicate key update blance=values(blance);";
                //echo $sql . '<br />';
                $sqls[] = $sql;
                $sqls[] = "update tuser_member set blance=$blance where userType='ADMIN' and siteID=$siteID;";
            }
        }
        if ($sqls) {
            $advM = new \Think\Model\AdvModel();
            $advM->patchQuery($sqls);
            echo 'success-';
        }
        echo 'done';
    }

    public function set_cti_port() {
        $port = I('port');
        $M = M();
        $sqls = array(
            "select * from asterisk.sip where keyword='port';
          select * from asterisk.digium_phones_general where keyword='mdns_port' and default_val=5060;
          select * from asterisk.endpoint_basefiles WHERE `value` = 5060;
          select * from asterisk.endpoint_global where `key`='sipPort';"
        );
        $sqls = array(
            "update asterisk.sip set data=$port where keyword='port'",
            "update asterisk.endpoint_global set  `values` = '$port' where `key`='sipPort'",
                // "update asterisk.digium_phones_general set default_val=$port where keyword='mdns_port';",
                // "update asterisk.endpoint_basefiles WHERE value = $port;",
        );
    }

    public function memcached_test() {
        $memcache = new \Memcache();
        $memcache->connect('127.0.0.1', 11211);
        $memcache->set('test', 'Memcache test successful!', 0, 60);
        $result = $memcache->get('test');
        unset($memcache);
        echo $result;
    }

    public function batch_update_cdr() {
        $this->display();
    }

    public function batch_update_cdr_save() {
        $date1 = I('date1');
        $date2 = I('date2', date('Y-m-d'));
        $page = I('page');
        $where = sprintf("date_format(start,'%%Y-%%m-%%d') >= '%s' and date_format(start,'%%Y-%%m-%%d') <= '%s'", $date1, $date2);
        $M = M('cdr');
        $count = $M->where($where)->count('id');
        if ($count && $page == 0) {
            $page = 1;
        }
        if ($page > 0) {
            $list = $M->where($where)->order('start desc')->page("$page,200")->getField('accountcode', true);
            $pageCount = ceil($count / 200);
            echo '正在处理第' . $page . '/' . $pageCount . '页...<br />';
            // echo 'select accountcode from cdr where '.sprintf("date_format(createdTime,'%%Y-%%m-%%d') >= '%s' and date_format(createdTime,'%%Y-%%m-%%d') <= '%s'", $date1, $date2).' order by start desc<br />';
            $i = 1;
            foreach ($list as $code) {
                if ($code) {
                    mysql_query("call proc_cdr_after_insert('$code');");
                    echo "$i: call proc_cdr_after_insert('$code');<br />";
                }
                $i++;
            }
            $page = $page + 1;
            if ($page > $pageCount) {
                $page = -1;
            }
            $url = AddonU('CtiTester/batch_update_cdr_save', "date1=$date1&date2=$date2&page=$page");
            $this->success('分页处理完成.', $url, 0);
        } else {
            echo '全部处理完成.<a href="' . AddonU('CtiTester/batch_update_cdr_save') . '">[返回]</a>';
        }
    }

}
